//
//  YWMaterialDBManager.m
//  maintain
//
//  Created by 烟雨云渊 on 16/9/1.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWDBTool.h"
#import "YWMaterialDBManager.h"

#import "YWApartment.h"
#import "YWFloor.h"
#import "YWLocation.h"
#import "YWMaterial.h"
#import "YWMaterialApply.h"
#import "YWMaterialApplyDetail.h"
#import "YWMaterialStockOut.h"
#import "YWMaterialStockOutDetail.h"
#import "YWMaterialRelation.h"
#import "YWMaterialReturn.h"
#import "YWMaterialReturnDetail.h"
#import "YWMaterialStock.h"
#import "YWReply.h"
#import "YWReplyDBManager.h"
#import "YWLoginTool.h"
#import "YWCommonTool.h"
#import "YWMaterialCirculation.h"

@implementation YWMaterialDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}

- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

- (void)createAllMaterialTables{
    //1.物料库存表
    [self createMaterialStockTable];
    //2.物料信息表
    [self createMaterialInfoTable];
    //3.物料申请表
    [self createMaterialApplyTable];
    //4.物料申请明细表
    [self createMaterialApplyDetailTable];
    //5.物料领用表
    [self createMaterialReceipientTable];
    //6.物料领用明细表
    [self createMaterialReceipientDetailTable];
    //7.物料归还表
    [self createMaterialReturnTable];
    //8.物料归还明细表
    [self createMaterialReturnDetailTable];
    //9.物料关系表 通过领用详情单获取申请详情单物料数量，通过归还详情单获取领用物料详情单数量时用到
    [self createMaterialRelationTable];
    //10.物料信息流转表
    [self createMaterialCirculationTable];
}

- (void)dropAndReCreateAllMaterialTables{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* materialStockSql = @"drop table if exists materialStock;";
        if ([db executeUpdate:materialStockSql]) {
            [db executeUpdate:KSqlCreateMaterialStockTable];
        }
        
        NSString* materialInfoSql = @"drop table if exists materialInfo;";
        if ([db executeUpdate:materialInfoSql]) {
            [db executeUpdate:kSqlCreateMaterialInfoTable];
        }
        
        NSString* materialApplySql = @"drop table if exists materialApply;";
        if ([db executeUpdate:materialApplySql]) {
            [db executeUpdate:kSqlCreateMaterialApplyTable];
        }
        
        NSString* materialApplyDetailSql = @"drop table if exists materialApplyDetail;";
        if ([db executeUpdate:materialApplyDetailSql]) {
            [db executeUpdate:kSqlCreateMaterialApplyDetailTable];
        }
        
        NSString* materialReceipientSql = @"drop table if exists materialReceipient;";
        if ([db executeUpdate:materialReceipientSql]) {
            [db executeUpdate:kSqlCreateMaterialReceipientTable];
        }
        
        NSString* materialReceipientDetailSql = @"drop table if exists materialReceipientDetail;";
        if ([db executeUpdate:materialReceipientDetailSql]) {
            [db executeUpdate:kSqlCreateMaterialReceipientDetailTable];
        }
        
        NSString* materialReturnSql = @"drop table if exists materialReturn;";
        if ([db executeUpdate:materialReturnSql]) {
            [db executeUpdate:kSqlCreateMaterialReturnTable];
        }
        
        NSString* materialReturnDetailSql = @"drop table if exists materialReturnDetail;";
        if ([db executeUpdate:materialReturnDetailSql]) {
            [db executeUpdate:kSqlCreateMaterialReturnDetailTable];
        }
        
        NSString* materialRelationSql = @"drop table if exists materialRelation;";
        if ([db executeUpdate:materialRelationSql]) {
            [db executeUpdate:kSqlCreateMaterialRelationTable];
        }
        
    }];
}

- (BOOL)addColumInApplyTable{
    __block BOOL executeResult = YES;
    //增加yearMonthDay字段
    if (![[self fmdb] columnExists:@"yearMonthDay" inTableWithName:@"materialApply"]) {
        [[self fmdb] executeUpdate:@"alter table materialApply add column yearMonthDay varchar;"];
    }
    return executeResult;
}

- (BOOL)addColumInReceipientTable{
    __block BOOL executeResult = YES;
    //增加yearMonthDay字段
    if (![[self fmdb] columnExists:@"yearMonthDay" inTableWithName:@"materialReceipient"]) {
        [[self fmdb] executeUpdate:@"alter table materialReceipient add column yearMonthDay varchar;"];
    }
    if (![[self fmdb] columnExists:@"carbonCopyIds" inTableWithName:@"materialReceipient"]) {
        [[self fmdb] executeUpdate:@"alter table materialReceipient add column carbonCopyIds varchar;"];
    }
    return executeResult;
}

- (BOOL)addColumInReturnTable{
    __block BOOL executeResult = YES;
    //增加yearMonthDay字段
    if (![[self fmdb] columnExists:@"yearMonthDay" inTableWithName:@"materialReturn"]) {
        [[self fmdb] executeUpdate:@"alter table materialReturn add column yearMonthDay varchar;"];
    }
    if (![[self fmdb] columnExists:@"carbonCopyIds" inTableWithName:@"materialReturn"]) {
        [[self fmdb] executeUpdate:@"alter table materialReturn add column carbonCopyIds varchar;"];
    }
    if (![[self fmdb] columnExists:@"status" inTableWithName:@"materialReturn"]) {
        [[self fmdb] executeUpdate:@"alter table materialReturn add column status integer;"];
    }
    return executeResult;
}

- (BOOL)addColumInMaterialApplyAndStockOutAndReturnBackTable{
    __block BOOL executeResult = YES;
    //增加materialRelationId字段
    if (![[self fmdb] columnExists:@"materialRelationId" inTableWithName:@"materialApply"]) {
        [[self fmdb] executeUpdate:@"alter table materialApply add column materialRelationId integer;"];
    }
    if (![[self fmdb] columnExists:@"materialRelationId" inTableWithName:@"materialReceipient"]) {
        [[self fmdb] executeUpdate:@"alter table materialReceipient add column materialRelationId integer;"];
    }
    if (![[self fmdb] columnExists:@"materialRelationId" inTableWithName:@"materialReturn"]) {
        [[self fmdb] executeUpdate:@"alter table materialReturn add column materialRelationId integer;"];
    }
    return executeResult;
}

#pragma mark - 库存表
NSString *const KSqlCreateMaterialStockTable = @"CREATE TABLE IF NOT EXISTS 'materialStock' (stockId integer,buildingId varchar,quantity integer,status integer,createTime timestamp,updateTime timestamp,apartmentId integer,floorId integer,locationId integer,materialInfoId integer);";

- (BOOL)createMaterialStockTable{
    return [[self fmdb] executeUpdate:KSqlCreateMaterialStockTable];
}

- (BOOL)updateMaterialStockWithArray:(NSArray*)materialStockArray
{
    if (materialStockArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialStockArray = materialStockArray;
    __block NSMutableArray* materialInfoArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialStockArray enumerateObjectsUsingBlock:^(YWMaterialStock* _Nonnull materialStock, NSUInteger idx, BOOL* _Nonnull stop) {
            if (materialStock.materialInfo) {
                [materialInfoArray addObject:materialStock.materialInfo];
            }

            sql = @"SELECT * FROM 'materialStock' WHERE stockId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialStock.stockId)];
            if ([set next]) {
                sql = @"UPDATE 'materialStock' SET buildingId =?,quantity =?,status =?,createTime =?,updateTime =?,apartmentId =?,floorId =?,locationId =?,materialInfoId =? WHERE stockId =?;";
                executeResult = [db executeUpdate:sql, materialStock.buildingId, @(materialStock.quantity), @(materialStock.status), @(materialStock.createTime), @(materialStock.updateTime), @(materialStock.apartment.apartmentId), @(materialStock.floor.floorId), @(materialStock.location.locationId), @(materialStock.materialInfo.materialInfoId), @(materialStock.stockId)];
            }
            else {
                sql = @"INSERT INTO 'materialStock' (stockId,buildingId,quantity,status,createTime,updateTime,apartmentId,floorId,locationId,materialInfoId) VALUES (?, ?, ?, ?, ?,?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, @(materialStock.stockId), materialStock.buildingId, @(materialStock.quantity), @(materialStock.status), @(materialStock.createTime), @(materialStock.updateTime), @(materialStock.apartment.apartmentId), @(materialStock.floor.floorId), @(materialStock.location.locationId), @(materialStock.materialInfo.materialInfoId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [self updateMaterialInfoWithArray:materialInfoArray];
    return executeResult;
}

- (NSArray*)materialStockWithPage:(NSInteger)page
{
    __block NSMutableArray* materialStockArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSInteger showCount = page * 10;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'materialStock' ORDER BY createTime DESC LIMIT %ld;", (long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWMaterialStock* materialStock = [self materialStockWithFMResultSet:set FMDatabase:db];
            [materialStockArray addObject:materialStock];
        }
        [db closeOpenResultSets];
    }];
    return materialStockArray.count ? materialStockArray : nil;
}

- (YWMaterialStock*)materialStockWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialStock* materialStock = [[YWMaterialStock alloc] init];
    materialStock.stockId = [set intForColumn:@"stockId"];
    materialStock.buildingId = [set stringForColumn:@"buildingId"];
    materialStock.quantity = [set intForColumn:@"quantity"];
    materialStock.status = [set intForColumn:@"status"];
    materialStock.createTime = [set longLongIntForColumn:@"createTime"];
    materialStock.updateTime = [set longLongIntForColumn:@"updateTime"];

    NSInteger apartmentId = [set intForColumn:@"apartmentId"];
    NSString* sql = @"SELECT * FROM 'apartment' where apartmentId=?;";
    FMResultSet* newSet = [db executeQuery:sql, @(apartmentId)];
    if ([newSet next]) {
        materialStock.apartment = [[YWDBTool shareDBTool] apartmentWithFMResultSet:newSet];
    }
    NSInteger floorId = [set intForColumn:@"floorId"];
    sql = @"SELECT * FROM 'floor' where floorId=?;";
    newSet = [db executeQuery:sql, @(floorId)];
    if ([newSet next]) {
        materialStock.floor = [[YWDBTool shareDBTool] floorWithFMResultSet:newSet];
    }
    NSInteger locationId = [set intForColumn:@"locationId"];
    sql = @"SELECT * FROM 'location' where locationId=?;";
    newSet = [db executeQuery:sql, @(locationId)];
    if ([newSet next]) {
        materialStock.location = [[YWDBTool shareDBTool] locationWithFMResultSet:newSet];
    }

    NSInteger materialInfoId = [set intForColumn:@"materialInfoId"];
    sql = @"SELECT * FROM 'materialInfo' where materialInfoId=?;";
    newSet = [db executeQuery:sql, @(materialInfoId)];
    if ([newSet next]) {
        materialStock.materialInfo = [self materialInfoWithFMResultSet:newSet FMDatabase:db];
    }
    return materialStock;
}

#pragma mark - 物料信息表
NSString*const kSqlCreateMaterialInfoTable = @"CREATE TABLE IF NOT EXISTS 'materialInfo' (materialInfoId integer,buildingId varchar,materialName varchar,materialCode varchar,modle varchar,brand varchar,unit varchar,typeId integer,status interger,createTime timestamp,updateTime timestamp);";

- (BOOL)createMaterialInfoTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialInfoTable];
}

- (BOOL)updateMaterialInfoWithArray:(NSArray*)materialInfoArray
{
    if (materialInfoArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialInfoArray = materialInfoArray;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialInfoArray enumerateObjectsUsingBlock:^(YWMaterial* _Nonnull materialInfo, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT * FROM 'materialInfo' WHERE materialInfoId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialInfo.materialInfoId)];
            if ([set next]) {
                sql = @"UPDATE 'materialInfo' SET buildingId =?,materialName =?,materialCode =?,modle =?,brand =?,unit =?,typeId =?,status =?,createTime =?,updateTime =? WHERE materialInfoId =?;";
                executeResult = [db executeUpdate:sql, materialInfo.buildingId, materialInfo.materialName, materialInfo.materialCode, materialInfo.model, materialInfo.brand, materialInfo.unit, @(materialInfo.typeId), @(materialInfo.status), @(materialInfo.createTime), @(materialInfo.updateTime), @(materialInfo.materialInfoId)];
            }
            else {
                sql = @"INSERT INTO 'materialInfo' (materialInfoId,buildingId,materialName,materialCode,modle,brand,unit,typeId,status,createTime,updateTime) VALUES (?, ?, ?, ?, ?,?, ?, ?, ?, ?,?);";
                executeResult = [db executeUpdate:sql, @(materialInfo.materialInfoId), materialInfo.buildingId, materialInfo.materialName, materialInfo.materialCode, materialInfo.model, materialInfo.brand, materialInfo.unit, @(materialInfo.typeId), @(materialInfo.status), @(materialInfo.createTime), @(materialInfo.updateTime)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (YWMaterial*)materialInfoWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterial* material = [[YWMaterial alloc] init];
    material.materialInfoId = [set intForColumn:@"materialInfoId"];
    material.buildingId = [set stringForColumn:@"buildingId"];
    material.materialName = [set stringForColumn:@"materialName"];
    material.materialCode = [set stringForColumn:@"materialCode"];
    material.model = [set stringForColumn:@"modle"];
    material.brand = [set stringForColumn:@"brand"];
    material.unit = [set stringForColumn:@"unit"];
    material.typeId = [set intForColumn:@"typeId"];
    material.status = [set intForColumn:@"status"];
    material.createTime = [set longLongIntForColumn:@"createTime"];
    material.updateTime = [set longLongIntForColumn:@"updateTime"];
    return material;
}

#pragma mark - 物料申请表
NSString*const kSqlCreateMaterialApplyTable = @"CREATE TABLE IF NOT EXISTS 'materialApply' (applyId integer,buildingId varchar,applySequence varchar,linkSequence varchar,creatorId integer,recipients varchar,copies varchar,note varchar,rejectNote varchar,createTime timestamp,updateTime timestamp,applyTime timestamp,yearMonthDay varchar,applyStatus integer,status integer,rejectUserId integer,materialRelationId integer);";

- (BOOL)createMaterialApplyTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialApplyTable];
}

//更新物料数据状态
- (BOOL)updateMaterialStatus:(NSInteger)status withApplyId:(NSInteger)applyId
{
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"SELECT * FROM 'materialApply' WHERE applyId=?;";
        FMResultSet* set = [db executeQuery:sql, @(applyId)];
        if ([set next]) {
            sql = @"UPDATE 'materialApply' SET status =? WHERE applyId =?;";
            executeResult = [db executeUpdate:sql, @(status), @(applyId)];
        }
        if (!executeResult) {
            *rollback = YES;
        };

        [db closeOpenResultSets];
    }];
    return executeResult;
}
//删除物料申请单
- (BOOL)deleteMaterialApplyWithApplyId:(NSInteger)applyId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"delete FROM 'materialApply' WHERE applyId=?";
        executeResult = [db executeUpdate:sql, @(applyId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
//更新物料申请状态
- (BOOL)updateMaterialApplyStatus:(NSInteger)applyStatus withApplyId:(NSInteger)applyId
{
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"SELECT * FROM 'materialApply' WHERE applyId=?;";
        FMResultSet* set = [db executeQuery:sql, @(applyId)];
        if ([set next]) {
            sql = @"UPDATE 'materialApply' SET applyStatus =? WHERE applyId =?;";
            executeResult = [db executeUpdate:sql, @(applyStatus), @(applyId)];
        }
        if (!executeResult) {
            *rollback = YES;
        };

        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (BOOL)updateMaterialApplyWithArray:(NSArray*)materialApplyArray
{
    if (materialApplyArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialApplyArray = materialApplyArray;
//    __block NSMutableArray* replysArray = [NSMutableArray array];
    __block NSMutableArray* applyDetailArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialApplyArray enumerateObjectsUsingBlock:^(YWMaterialApply* _Nonnull materialApply, NSUInteger idx, BOOL* _Nonnull stop) {
            //sql = @"DELETE FROM 'reply' WHERE relationId=?";
            //[db executeUpdate:sql, @(materialApply.applyId)];
//            if (materialApply.replies) {
//                [materialApply.replies enumerateObjectsUsingBlock:^(YWReply* _Nonnull reply, NSUInteger idx, BOOL* _Nonnull stop) {
//                    reply.relationId = materialApply.applyId;
//                    reply.relationType = YWReplyRelationTypeMaterialApply;
//                }];
//                [replysArray addObjectsFromArray:materialApply.replies];
//            }

            [materialApply.details enumerateObjectsUsingBlock:^(YWMaterialApplyDetail* _Nonnull applyDetail, NSUInteger idx, BOOL* _Nonnull stop) {
                applyDetail.applyId = materialApply.applyId;
            }];
            [applyDetailArray addObjectsFromArray:materialApply.details];

            sql = @"SELECT * FROM 'materialApply' WHERE applyId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialApply.applyId)];
            if ([set next]) {
                sql = @"UPDATE 'materialApply' SET buildingId =?,applySequence =?,linkSequence =?,creatorId =?,recipients =?,copies =?,note =?,rejectNote =?,createTime =?,updateTime =?,applyTime =?,yearMonthDay =?,applyStatus =?,status =?,rejectUserId =?,materialRelationId =? WHERE applyId =?;";
                executeResult = [db executeUpdate:sql, materialApply.buildingId, materialApply.applySequence, materialApply.linkSequence, @(materialApply.creator.uniqueId), materialApply.receiverIds, materialApply.carbonCopyIds, materialApply.note, materialApply.rejectNote, @(materialApply.createTime), @(materialApply.updateTime), @(materialApply.applyTime), materialApply.yearMonthDay,@(materialApply.applyStatus), @(materialApply.status),@(materialApply.rejectUser.uniqueId),@(materialApply.materialRelationId), @(materialApply.applyId)];
            }
            else {
                sql = @"INSERT INTO 'materialApply' (applyId,buildingId,applySequence,linkSequence,creatorId,recipients,copies,note,rejectNote,createTime,updateTime,applyTime,yearMonthDay,applyStatus,status,rejectUserId,materialRelationId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(materialApply.applyId), materialApply.buildingId, materialApply.applySequence, materialApply.linkSequence, @(materialApply.creator.uniqueId), materialApply.receiverIds, materialApply.carbonCopyIds, materialApply.note, materialApply.rejectNote, @(materialApply.createTime), @(materialApply.updateTime), @(materialApply.applyTime),materialApply.yearMonthDay, @(materialApply.applyStatus), @(materialApply.status),@(materialApply.rejectUser.uniqueId),@(materialApply.materialRelationId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];

//    [[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
    [self updateMaterialApplyDetailWithArray:applyDetailArray];

    return executeResult;
}

- (NSArray*)materialApplyWithPage:(NSInteger)page
{
    __block NSMutableArray* materialApplyArray = [NSMutableArray array];
//    __block NSMutableArray *applyTimeArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
//    NSInteger userId = [YWLoginTool loginUser].uniqueId;
    /*
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *selectSql = [NSString stringWithFormat:@"SELECT distinct yearMonthDay FROM 'materialApply' ORDER BY applyTime DESC,status DESC"];
        // WHERE creatorId =%ld OR recipients = %ld OR copies =%ld
        FMResultSet* resuleSet = [db executeQuery:selectSql];
        while ([resuleSet next]) {
            NSString *yearMonthDay = [resuleSet stringForColumn:@"yearMonthDay"];
            if (![applyTimeArray containsObject:yearMonthDay]) {
                [applyTimeArray addObject:yearMonthDay];
            }
        }
        [applyTimeArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull yearMonthDay, NSUInteger idx, BOOL * _Nonnull stop) {
            NSMutableArray *tempArray = [NSMutableArray array];
            NSString *sql = [NSString stringWithFormat:@"SELECT * FROM 'materialApply' WHERE yearMonthDay = ?ORDER BY status DESC, applyTime DESC LIMIT %ld;",(long)showCount];
            FMResultSet* set = [db executeQuery:sql, yearMonthDay];
            while ([set next]) {
                YWMaterialApplyViewModel * apply = [self materialApplyWithFMResultSet:set FMDatabase:db];
                [tempArray addObject:apply];
            }
            if (tempArray.count>0) {
                [materialApplyArray addObject:tempArray];
            }
        }];
        */
        [self.dbQeue inDatabase:^(FMDatabase* db) {
            NSString* sql = nil;
            FMResultSet* set = nil;
            sql = [NSString stringWithFormat:@"SELECT * FROM 'materialApply' ORDER BY status DESC,applyTime DESC LIMIT %lu",(long)showCount];
            set = [db executeQuery:sql];
            
            NSString *lastDate = nil;
            NSMutableArray *array = nil;
            while ([set next]) {
                YWMaterialApplyViewModel * apply = [self materialApplyWithFMResultSet:set FMDatabase:db];
                NSString *timeDate = [YWCommonTool showChineseFormatterDate:apply.applyTime/1000];
                if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                    lastDate = timeDate;
                    array = [NSMutableArray array];
                }
                [array addObject:apply];
                if (![materialApplyArray containsObject:array] && array.count) {
                    [materialApplyArray addObject:array];
                }
            }
//        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'materialApply' ORDER BY status DESC,applyStatus ASC,applyTime DESC LIMIT %ld;", (long)showCount];
//        FMResultSet* set = [db executeQuery:sql];
//        while ([set next]) {
//            YWMaterialApplyViewModel* materialApplyViewModel = [self materialApplyWithFMResultSet:set FMDatabase:db];
//            [materialApplyArray addObject:materialApplyViewModel];
//        }
        [db closeOpenResultSets];
    }];
    return materialApplyArray.count ? materialApplyArray : nil;
    
}

- (YWMaterialApplyViewModel*)materialApplyWithMaterialApplyId:(NSInteger)materialApplyId
{
    __block YWMaterialApplyViewModel* viewModel = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialApply' WHERE applyId = ? ;";
        //AND applyStatus=0
        FMResultSet* set = [db executeQuery:sql, @(materialApplyId)];
        if ([set next]) {
            viewModel = [self materialApplyWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return viewModel;
}

- (YWMaterialApplyViewModel*)materialApplyWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialApplyViewModel* materialApplyViewModel = [[YWMaterialApplyViewModel alloc] init];
    NSInteger applyId = [set intForColumn:@"applyId"];
    __block NSMutableArray* materialInfoArray = [NSMutableArray array];
//    NSString* sql = @"SELECT * FROM 'materialInfo' WHERE materialInfoId =(SELECT materialInfoId FROM 'materialStock' WHERE stockId = (SELECT stockId FROM 'materialApplyDetail' WHERE applyId = ?))";
    NSString* sql = @"select i.* from 'materialApplyDetail' d left join 'materialStock' s on s.stockId = d.stockId left join 'materialInfo' i on i.materialInfoId = s.materialInfoId where d.applyId = ?";
    FMResultSet* appleset = [db executeQuery:sql, @(applyId)];
    while ([appleset next]) {
        YWMaterial* material = [self materialInfoWithFMResultSet:appleset FMDatabase:db];
        [materialInfoArray addObject:material];
    }

    materialApplyViewModel.applyId = [set intForColumn:@"applyId"];
    materialApplyViewModel.materialRelationId = [set intForColumn:@"materialRelationId"];
    materialApplyViewModel.applySequence = [set stringForColumn:@"applySequence"];
    
    materialApplyViewModel.yearMonthDay = [set stringForColumn:@"yearMonthDay"];
    
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(creatorId)];
    if ([authorSet next]) {
        materialApplyViewModel.creator = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
    }

    NSInteger rejectUserId = [set intForColumn:@"rejectUserId"];
    NSString* userSql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* userSet = [db executeQuery:userSql, @(rejectUserId)];
     if  ([userSet next]) {
         materialApplyViewModel.rejectUser = [[YWDBTool shareDBTool] handlerWithSet:userSet];
     }
    
    NSString* receiverIds = [set stringForColumn:@"recipients"];
    NSArray* idsArray = [receiverIds componentsSeparatedByString:@","];
    NSMutableArray* receivers = [NSMutableArray array];
    [idsArray enumerateObjectsUsingBlock:^(NSString* _Nonnull uniqueId, NSUInteger idx, BOOL* _Nonnull stop) {
        NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
        FMResultSet* authorSet = [db executeQuery:authorsql, uniqueId];
        if ([authorSet next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
            [receivers addObject:handler];
        }
    }];
    materialApplyViewModel.receivers = receivers;

    //读出的copyids是一个字符串，要通过遍历把它拼成一个数组然后再赋值
    NSString* copyIds = [set stringForColumn:@"copies"];
    NSArray* copidsArray = [copyIds componentsSeparatedByString:@","];
    NSMutableArray* copies = [NSMutableArray array];
    [copidsArray enumerateObjectsUsingBlock:^(NSString* _Nonnull uniqueId, NSUInteger idx, BOOL* _Nonnull stop) {
        NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
        FMResultSet* authorSet = [db executeQuery:authorsql, uniqueId];
        if ([authorSet next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
            [copies addObject:handler];
        }
    }];
    materialApplyViewModel.observers = copies;

    materialApplyViewModel.applyTime = [set longLongIntForColumn:@"applyTime"];
    materialApplyViewModel.updateTime = [set longLongIntForColumn:@"updateTime"];
    materialApplyViewModel.applyStatus = [set intForColumn:@"applyStatus"];
    materialApplyViewModel.status = [set intForColumn:@"status"];
    materialApplyViewModel.linkSequence = [set stringForColumn:@"linkSequence"];
    materialApplyViewModel.note = [set stringForColumn:@"note"];
    
    NSMutableArray* tempArray = [NSMutableArray array];
    sql = @"SELECT * FROM 'reply' WHERE relationId=? AND relationType=? ORDER BY replyTime;";
    FMResultSet* newset = [db executeQuery:sql, @(applyId), @(YWReplyRelationTypeMaterialApply)];
    while ([newset next]) {
        YWReply* reply = [[YWReplyDBManager shareManager] replyWithFMResultSet:newset FMDatabase:db];
        [tempArray addObject:reply];
    }
    materialApplyViewModel.replies = tempArray;

    NSMutableArray* detailArray = [NSMutableArray array];
    sql = @"SELECT * FROM 'materialApplyDetail' WHERE applyId =?;";
    FMResultSet* detailSet = [db executeQuery:sql, @(applyId)];
    while ([detailSet next]) {
        YWMaterialApplyDetail* detail = [self materialApplyDetailWithFMResultSet:detailSet FMDatabase:db];
        [detailArray addObject:detail];
    }
    materialApplyViewModel.details = detailArray;
    return materialApplyViewModel;
}

#pragma mark - 物料申请明细表
NSString*const kSqlCreateMaterialApplyDetailTable = @"CREATE TABLE IF NOT EXISTS 'materialApplyDetail' (detailId integer,applyId integer,stockId integer,quantity integer,createTime timestamp,updateTime timestamp,status smallint);";

- (BOOL)createMaterialApplyDetailTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialApplyDetailTable];
}

- (BOOL)updateMaterialApplyDetailWithArray:(NSArray*)materialApplyDetailArray
{
    if (materialApplyDetailArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialApplyDetailArray = materialApplyDetailArray;
    __block NSMutableArray* stockArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialApplyDetailArray enumerateObjectsUsingBlock:^(YWMaterialApplyDetail* _Nonnull materialApplyDetail, NSUInteger idx, BOOL* _Nonnull stop) {
            if (materialApplyDetail.materialStock) {
                [stockArray addObject:materialApplyDetail.materialStock];
            }

            sql = @"SELECT * FROM 'materialApplyDetail' WHERE detailId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialApplyDetail.detailId)];

            if ([set next]) {
                sql = @"UPDATE 'materialApplyDetail' SET applyId =?,stockId =?,quantity =?,createTime =?,updateTime =?,status =? WHERE detailId =?;";
                executeResult = [db executeUpdate:sql, @(materialApplyDetail.applyId), @(materialApplyDetail.materialStock.stockId), @(materialApplyDetail.quantity), @(materialApplyDetail.createTime), @(materialApplyDetail.updateTime), @(materialApplyDetail.status), @(materialApplyDetail.detailId)];
            }
            else {
                sql = @"INSERT INTO 'materialApplyDetail' (detailId,applyId,stockId,quantity,createTime,updateTime,status) VALUES (?, ?, ?, ?, ?,?, ?);";
                executeResult = [db executeUpdate:sql, @(materialApplyDetail.detailId), @(materialApplyDetail.applyId), @(materialApplyDetail.materialStock.stockId), @(materialApplyDetail.quantity), @(materialApplyDetail.createTime), @(materialApplyDetail.updateTime), @(materialApplyDetail.status)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [self updateMaterialStockWithArray:stockArray];
    return executeResult;
}

- (YWMaterialApplyDetail*)materialApplyDetailWithMaterialApplyDetailId:(NSInteger)materialApplyDetailId
{
    __block YWMaterialApplyDetail* materialApplyDetail = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialApplyDetail' WHERE detailId = ?;";
        FMResultSet* set = [db executeQuery:sql, @(materialApplyDetailId)];
        if ([set next]) {
            materialApplyDetail = [self materialApplyDetailWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialApplyDetail;
}

- (YWMaterialApplyDetail*)materialApplyDetailWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialApplyDetail* materialApplyDetail = [[YWMaterialApplyDetail alloc] init];
    materialApplyDetail.detailId = [set intForColumn:@"detailId"];
    materialApplyDetail.applyId = [set intForColumn:@"applyId"];

    NSInteger stockId = [set intForColumn:@"stockId"];
    NSString* authorsql = @"SELECT * FROM 'materialStock' WHERE stockId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(stockId)];
    if ([authorSet next]) {
        materialApplyDetail.materialStock = [self materialStockWithFMResultSet:authorSet FMDatabase:db];
    }

    materialApplyDetail.quantity = [set intForColumn:@"quantity"];
    materialApplyDetail.createTime = [set longLongIntForColumn:@"createTime"];
    materialApplyDetail.updateTime = [set longLongIntForColumn:@"updateTime"];
    materialApplyDetail.status = [set intForColumn:@"status"];
    return materialApplyDetail;
}

#pragma mark - 物料领用表的创建、更新、取值
NSString*const kSqlCreateMaterialReceipientTable = @"CREATE TABLE IF NOT EXISTS 'materialReceipient' (stockOutId integer,buildingId varchar,stockOutSequence varchar,creatorId integer,receiverId integer,carbonCopyIds varchar,note varchar,linkSequence varchar,stockOutTime timestamp,yearMonthDay varchar,stockOutStatus integer,createTime timestamp,updateTime timestamp,status integer,applyId integer,materialRelationId integer);";

- (BOOL)createMaterialReceipientTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialReceipientTable];
}

- (BOOL)deleteMaterialStockOutWithStockOutId:(NSInteger)stockOutId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"delete FROM 'materialReceipient' WHERE stockOutId=?";
        executeResult = [db executeUpdate:sql, @(stockOutId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateMaterialStockOutStatusWithStockOutId:(NSInteger)stockOutId
{
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"SELECT * FROM 'materialReceipient' WHERE stockOutId=?;";
        FMResultSet* set = [db executeQuery:sql, @(stockOutId)];
        if ([set next]) {
            sql = @"UPDATE 'materialReceipient' SET stockOutStatus =? WHERE stockOutId =?;";
            executeResult = [db executeUpdate:sql, @(YWMaterialStatusHaveStockout), @(stockOutId)];
        }
        if (!executeResult) {
            *rollback = YES;
        };

        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateMaterialReceipientWithArray:(NSArray*)materialReceipientArray
{
    if (materialReceipientArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialReceipientArray = materialReceipientArray;
//    __block NSMutableArray* replysArray = [NSMutableArray array];
    __block NSMutableArray* receipientArray = [NSMutableArray array];
    __block NSMutableArray* applyArray = [NSMutableArray array];
    __block NSMutableArray* relationArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialReceipientArray enumerateObjectsUsingBlock:^(YWMaterialStockOut* _Nonnull materialReceipient, NSUInteger idx, BOOL* _Nonnull stop) {
            //sql = @"DELETE FROM 'reply' WHERE relationId=?";
            //[db executeUpdate:sql, @(materialReceipient.stockOutId)];
//            if (materialReceipient.replies) {
//                [materialReceipient.replies enumerateObjectsUsingBlock:^(YWReply* _Nonnull reply, NSUInteger idx, BOOL* _Nonnull stop) {
//                    reply.relationId = materialReceipient.stockOutId;
//                    reply.relationType = YWReplyRelationTypeMaterialReceive;
//                }];
//                [replysArray addObjectsFromArray:materialReceipient.replies];
//            }
            if (materialReceipient.materialApply) {
                [applyArray addObject:materialReceipient.materialApply];
            }
            if (materialReceipient.stockOutStatus != YWMaterialStatusNoReceive) {
                YWMaterialRelation *relation = [[YWMaterialRelation alloc] init];
                if (materialReceipient.materialApply) {
                    relation.applyId = materialReceipient.materialApply.applyId;
                    relation.stockOutId = materialReceipient.stockOutId;
                    [relationArray addObject:relation];
                }
            }            
            
            [materialReceipient.details enumerateObjectsUsingBlock:^(YWMaterialStockOutDetail* _Nonnull receipientDetail, NSUInteger idx, BOOL* _Nonnull stop) {
                receipientDetail.stockOutId = materialReceipient.stockOutId;
            }];
            [receipientArray addObjectsFromArray:materialReceipient.details];

            sql = @"SELECT * FROM 'materialReceipient' WHERE stockOutId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialReceipient.stockOutId)];
            if ([set next]) {
                sql = @"UPDATE 'materialReceipient' SET buildingId =?,stockOutSequence =?,creatorId =?,receiverId =?,carbonCopyIds =?,note =?,linkSequence =?,stockOutTime =?,yearMonthDay =?,stockOutStatus =?,createTime =?,updateTime =?,status =?,applyId =?,materialRelationId =? WHERE stockOutId =?;";
                executeResult = [db executeUpdate:sql, materialReceipient.buildingId, materialReceipient.stockOutSequence, @(materialReceipient.creator.uniqueId), @(materialReceipient.receiver.uniqueId),materialReceipient.carbonCopyIds, materialReceipient.note, materialReceipient.linkSequence, @(materialReceipient.stockOutTime),materialReceipient.yearMonthDay, @(materialReceipient.stockOutStatus), @(materialReceipient.createTime), @(materialReceipient.updateTime), @(materialReceipient.status), @(materialReceipient.materialApply.applyId),@(materialReceipient.materialRelationId), @(materialReceipient.stockOutId)];
            }
            else {
                sql = @"INSERT INTO 'materialReceipient' (stockOutId,buildingId,stockOutSequence,creatorId,receiverId,carbonCopyIds,note,linkSequence,stockOutTime,yearMonthDay,stockOutStatus,createTime,updateTime,status,applyId,materialRelationId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(materialReceipient.stockOutId), materialReceipient.buildingId, materialReceipient.stockOutSequence, @(materialReceipient.creator.uniqueId), @(materialReceipient.receiver.uniqueId),materialReceipient.carbonCopyIds, materialReceipient.note, materialReceipient.linkSequence, @(materialReceipient.stockOutTime),materialReceipient.yearMonthDay, @(materialReceipient.stockOutStatus), @(materialReceipient.createTime), @(materialReceipient.updateTime), @(materialReceipient.status), @(materialReceipient.materialApply.applyId),@(materialReceipient.materialRelationId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
//    [[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
    [self updateMaterialReceipientDetailWithArray:receipientArray];
    [self updateMaterialApplyWithArray:applyArray];
    [self updateMaterialRelationWithArray:relationArray];
    return executeResult;
}

- (NSArray*)materialReceipientWithPage:(NSInteger)page
{
    __block NSMutableArray* materialReceipientArray = [NSMutableArray array];
//    __block NSMutableArray * stockOutTimeArray = [NSMutableArray  array];
//    NSInteger userId = [YWLoginTool loginUser].uniqueId;
    NSInteger showCount = page * 10;
    /*
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        //通过yearMonthDay去重
        NSString *selectSql = [NSString stringWithFormat:@"SELECT distinct yearMonthDay FROM 'materialReceipient' WHERE stockOutStatus = %lu OR stockOutStatus = %lu ORDER BY stockOutTime DESC;",(unsigned long)YWMaterialStatusHaveStockout,(unsigned long)YWMaterialStatusHaveReturned];
        //creatorId =%ld OR receiverId = %ld  OR carbonCopyIds =%ld AND
        FMResultSet* resuleSet = [db executeQuery:selectSql];
        while ([resuleSet next]) {
            NSString *yearMonthDay = [resuleSet stringForColumn:@"yearMonthDay"];
            if (![stockOutTimeArray containsObject:yearMonthDay]) {
                [stockOutTimeArray addObject:yearMonthDay];
            }
        }
        [stockOutTimeArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull yearMonthDay, NSUInteger idx, BOOL * _Nonnull stop) {
            NSMutableArray *tempArray = [NSMutableArray array];
            NSString *sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReceipient' WHERE yearMonthDay = ? ORDER BY stockOutTime DESC LIMIT %ld;",(long)showCount];
            FMResultSet* set = [db executeQuery:sql, yearMonthDay];
            while ([set next]) {
                YWMaterialStockOut* receipientViewModel = [self materialReceipientFMResultSet:set FMDatabase:db];
                [tempArray addObject:receipientViewModel];
            }
            if (tempArray.count>0) {
                [materialReceipientArray addObject:tempArray];
            }
        }];
*/
        [self.dbQeue inDatabase:^(FMDatabase* db) {
            NSString* sql = nil;
            FMResultSet* set = nil;
            sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReceipient' WHERE stockOutStatus = %lu OR stockOutStatus = %lu ORDER BY stockOutTime DESC LIMIT %ld;",(unsigned long)YWMaterialStatusHaveStockout,(unsigned long)YWMaterialStatusHaveReturned,(long)showCount];
            set = [db executeQuery:sql];
            
            NSString *lastDate = nil;
            NSMutableArray *array = nil;
            while ([set next]) {
                YWMaterialStockOut* receipientViewModel = [self materialReceipientFMResultSet:set FMDatabase:db];
                NSString *timeDate = [YWCommonTool showChineseFormatterDate:receipientViewModel.stockOutTime/1000];
                if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                    lastDate = timeDate;
                    array = [NSMutableArray array];
                }
                [array addObject:receipientViewModel];
                if (![materialReceipientArray containsObject:array] && array.count) {
                    [materialReceipientArray addObject:array];
                }
            }

        
//        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReceipient' WHERE stockOutStatus = %d OR stockOutStatus = %d ORDER BY stockOutStatus ASC ,stockOutTime DESC LIMIT %ld;",YWMaterialStatusHaveStockout,YWMaterialStatusHaveReturned, (long)showCount];
//        //未领用的状态要做处理
//        FMResultSet* set = [db executeQuery:sql];
//        while ([set next]) {
//            YWMaterialStockOut* receipientViewModel = [self materialReceipientFMResultSet:set FMDatabase:db];
//            [materialReceipientArray addObject:receipientViewModel];
//        }
        [db closeOpenResultSets];
    }];
    return materialReceipientArray.count ? materialReceipientArray : nil;
}
- (YWMaterialStockOut*)materialReceipientFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialStockOut* viewModel = [[YWMaterialStockOut alloc] init];
    viewModel.materialRelationId = [set intForColumn:@"materialRelationId"];
    viewModel.stockOutSequence = [set stringForColumn:@"stockOutSequence"];
    viewModel.stockOutId = [set intForColumn:@"stockOutId"];
    viewModel.buildingId = [set stringForColumn:@"buildingId"];
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(creatorId)];
    if ([authorSet next]) {
        viewModel.creator = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
    }
    viewModel.yearMonthDay = [set stringForColumn:@"yearMonthDay"];
    NSInteger receiverId = [set intForColumn:@"receiverId"];
    NSString* receiversql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* receiverSet = [db executeQuery:receiversql, @(receiverId)];
    if ([receiverSet next]) {
        viewModel.receiver = [[YWDBTool shareDBTool] handlerWithSet:receiverSet];
    }
    viewModel.note = [set stringForColumn:@"note"];
    viewModel.stockOutTime = [set longLongIntForColumn:@"stockOutTime"];
    viewModel.stockOutStatus = [set intForColumn:@"stockOutStatus"];
    viewModel.createTime = [set longLongIntForColumn:@"createTime"];
    viewModel.updateTime = [set longLongIntForColumn:@"updateTime"];

    NSInteger stockOutId = [set intForColumn:@"stockOutId"];
    NSMutableArray* detailArray = [NSMutableArray array];
    NSString* sql = @"SELECT * FROM 'materialReceipientDetail' WHERE stockOutId = ?;";
    FMResultSet* detailSet = [db executeQuery:sql, @(stockOutId)];
    while ([detailSet next]) {
        YWMaterialStockOutDetail* detail = [self materialReceipientDetailFMResultSet:detailSet FMDatabase:db];
        [detailArray addObject:detail];
    }
    viewModel.details = detailArray;
    
    //读出的copyids是一个字符串，要通过遍历把它拼成一个数组然后再赋值
    NSString* copyIds = [set stringForColumn:@"carbonCopyIds"];
    NSArray* copidsArray = [copyIds componentsSeparatedByString:@","];
    NSMutableArray* copies = [NSMutableArray array];
    [copidsArray enumerateObjectsUsingBlock:^(NSString* _Nonnull uniqueId, NSUInteger idx, BOOL* _Nonnull stop) {
        NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
        FMResultSet* authorSet = [db executeQuery:authorsql, uniqueId];
        if ([authorSet next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
            [copies addObject:handler];
        }
    }];
    viewModel.observers = copies;

    NSMutableArray* tempArray = [NSMutableArray array];
    sql = @"SELECT * FROM 'reply' WHERE relationId=? AND relationType=? ORDER BY replyTime;";
    FMResultSet* newset = [db executeQuery:sql, @(stockOutId), @(YWReplyRelationTypeMaterialReceive)];
    while ([newset next]) {
        YWReply* reply = [[YWReplyDBManager shareManager] replyWithFMResultSet:newset FMDatabase:db];
        [tempArray addObject:reply];
    }
    viewModel.replies = tempArray;

    NSInteger applyId = [set intForColumn:@"applyId"];
    sql = @"SELECT * FROM 'materialApply' where applyId=?;";
    FMResultSet* newSet = [db executeQuery:sql, @(applyId)];
    if ([newSet next]) {
        viewModel.apply = [self materialApplyWithFMResultSet:newSet FMDatabase:db];
    }
    return viewModel;
}

- (YWMaterialStockOut*)materialReceipientWithMaterialReceipientId:(NSInteger)materialReceipientId
{
    __block YWMaterialStockOut* materialReceipient = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialReceipient' WHERE stockOutId = ? ;";
        //AND stockOutStatus =1
        FMResultSet* set = [db executeQuery:sql, @(materialReceipientId)];

        if ([set next]) {
            materialReceipient = [self materialReceipientFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialReceipient;
}
- (YWMaterialApplyViewModel *)materialApplyWithMaterialStockOutId:(NSInteger)materialStockOutId{
    __block YWMaterialApplyViewModel* applyViewModel = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialApply' WHERE applyId = (SELECT applyId FROM 'materialReceipient' where stockOutId =?);";
        FMResultSet* set = [db executeQuery:sql, @(materialStockOutId)];
        
        if ([set next]) {
            applyViewModel = [self materialApplyWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return applyViewModel;
}
#pragma mark - 物料领用明细表更新、取值
NSString*const kSqlCreateMaterialReceipientDetailTable = @"CREATE TABLE IF NOT EXISTS 'materialReceipientDetail' (detailId integer,stockOutId integer,stockId integer,quantity integer,createTime timestamp,updateTime timestamp,status integer);";

- (BOOL)createMaterialReceipientDetailTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialReceipientDetailTable];
}

- (BOOL)updateMaterialReceipientDetailWithArray:(NSArray*)materialReceipientDetailArray
{
    if (materialReceipientDetailArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialReceipientDetailArray = materialReceipientDetailArray;
    __block NSMutableArray* materialStock = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialReceipientDetailArray enumerateObjectsUsingBlock:^(YWMaterialStockOutDetail* _Nonnull materialReceipientDetail, NSUInteger idx, BOOL* _Nonnull stop) {
            if (materialReceipientDetail.materialStock) {
                [materialStock addObject:materialReceipientDetail.materialStock];
            }
            sql = @"SELECT * FROM 'materialReceipientDetail' WHERE detailId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialReceipientDetail.detailId)];
            if ([set next]) {
                sql = @"UPDATE 'materialReceipientDetail' SET stockOutId =?,stockId =?,quantity =?,createTime =?,updateTime =?,status =? WHERE detailId =?;";
                executeResult = [db executeUpdate:sql, @(materialReceipientDetail.stockOutId), @(materialReceipientDetail.materialStock.stockId), @(materialReceipientDetail.quantity), @(materialReceipientDetail.createTime), @(materialReceipientDetail.updateTime), @(materialReceipientDetail.status), @(materialReceipientDetail.detailId)];
            }
            else {
                sql = @"INSERT INTO 'materialReceipientDetail' (detailId,stockOutId,stockId,quantity,createTime,updateTime,status) VALUES (?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, @(materialReceipientDetail.detailId), @(materialReceipientDetail.stockOutId), @(materialReceipientDetail.materialStock.stockId), @(materialReceipientDetail.quantity), @(materialReceipientDetail.createTime), @(materialReceipientDetail.updateTime), @(materialReceipientDetail.status)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [self updateMaterialStockWithArray:materialStock];
    return executeResult;
}

- (YWMaterialStockOutDetail*)materialReceipientDetailFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialStockOutDetail* materialReceipientDetail = [[YWMaterialStockOutDetail alloc] init];
    materialReceipientDetail.detailId = [set intForColumn:@"detailId"];
    materialReceipientDetail.stockOutId = [set intForColumn:@"stockOutId"];
    NSInteger stockId = [set intForColumn:@"stockId"];
    NSString* authorsql = @"SELECT * FROM 'materialStock' WHERE stockId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(stockId)];
    if ([authorSet next]) {
        materialReceipientDetail.materialStock = [self materialStockWithFMResultSet:authorSet FMDatabase:db];
    }
    materialReceipientDetail.quantity = [set intForColumn:@"quantity"];
    materialReceipientDetail.createTime = [set longLongIntForColumn:@"createTime"];
    materialReceipientDetail.updateTime = [set longLongIntForColumn:@"updateTime"];
    materialReceipientDetail.status = [set intForColumn:@"status"];
    return materialReceipientDetail;
}
#pragma mark - 物料归还表更新、取值
NSString*const kSqlCreateMaterialReturnTable = @"CREATE TABLE IF NOT EXISTS 'materialReturn' (returnId integer,buildingId varchar,returnSequence varchar,creatorId integer,carbonCopyIds varchar,returnerId integer,note varchar,linkSequence varchar,createTime timestamp,returnTime timestamp,yearMonthDay varchar,returnStatus integer,status integer,updateTime timestamp,stockOutId integer,materialRelationId integer);";

- (BOOL)createMaterialReturnTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialReturnTable];
}
- (BOOL)updateMaterialReturnWithArray:(NSArray*)materialReturnArray
{
    if (materialReturnArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialReturnArray = materialReturnArray;
    __block NSMutableArray* returnDetailArray = [NSMutableArray array];
//    __block NSMutableArray* replysArray = [NSMutableArray array];
    __block NSMutableArray *stockOutArray = [NSMutableArray array];
    __block NSMutableArray *relationArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialReturnArray enumerateObjectsUsingBlock:^(YWMaterialReturn* _Nonnull materialReturn, NSUInteger idx, BOOL* _Nonnull stop) {
            //sql = @"DELETE FROM 'reply' WHERE relationId=?";
            //[db executeUpdate:sql, @(materialReturn.returnId)];
//            if (materialReturn.replies) {
//                [materialReturn.replies enumerateObjectsUsingBlock:^(YWReply* _Nonnull reply, NSUInteger idx, BOOL* _Nonnull stop) {
//                    reply.relationId = materialReturn.returnId;
//                    reply.relationType = YWReplyRelationTypeMaterialReturn;
//                }];
//                [replysArray addObjectsFromArray:materialReturn.replies];
//            }
            [materialReturn.details enumerateObjectsUsingBlock:^(YWMaterialReturnDetail* _Nonnull returnDetail, NSUInteger idx, BOOL* _Nonnull stop) {
                returnDetail.returnId = materialReturn.returnId;
            }];
            [returnDetailArray addObjectsFromArray:materialReturn.details];
            if (materialReturn.materialStockOut) {
                [stockOutArray addObject:materialReturn.materialStockOut];
            }
            if (materialReturn.returnStatus == YWMaterialStatusHaveReturned) {
                YWMaterialRelation * materialRelation = [[YWMaterialRelation alloc] init];
                materialRelation.stockOutId = materialReturn.materialStockOut.stockOutId;
                materialRelation.returnId = materialReturn.returnId;
                
                [relationArray addObject:materialRelation];
            }
 
            sql = @"SELECT * FROM 'materialReturn' WHERE returnId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialReturn.returnId)];
            if ([set next]) {
                sql = @"UPDATE 'materialReturn' SET buildingId =?,returnSequence =?,returnerId =?,creatorId =?,carbonCopyIds =?,note =?,linkSequence =?,createTime =?,returnTime =?,yearMonthDay =?,returnStatus =?,status =?,updateTime =?,stockOutId =?,materialRelationId =? WHERE returnId =?;";
                executeResult = [db executeUpdate:sql, materialReturn.buildingId, materialReturn.returnSequence, @(materialReturn.returner.uniqueId), @(materialReturn.creator.uniqueId),materialReturn.carbonCopyIds, materialReturn.note, materialReturn.linkSequence, @(materialReturn.createTime), @(materialReturn.returnTime),materialReturn.yearMonthDay, @(materialReturn.returnStatus),@(materialReturn.status), @(materialReturn.updateTime), @(materialReturn.materialStockOut.stockOutId),@(materialReturn.materialRelationId), @(materialReturn.returnId)];
            }
            else {
                sql = @"INSERT INTO 'materialReturn' (returnId,buildingId,returnSequence,returnerId,creatorId,carbonCopyIds,note,linkSequence,returnTime,yearMonthDay,returnStatus,status,createTime,updateTime,stockOutId,materialRelationId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(materialReturn.returnId), materialReturn.buildingId, materialReturn.returnSequence, @(materialReturn.returner.uniqueId), @(materialReturn.creator.uniqueId), materialReturn.carbonCopyIds,materialReturn.note, materialReturn.linkSequence, @(materialReturn.returnTime),materialReturn.yearMonthDay, @(materialReturn.returnStatus),@(materialReturn.status), @(materialReturn.createTime), @(materialReturn.updateTime), @(materialReturn.materialStockOut.stockOutId),@(materialReturn.materialRelationId)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
//    [[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
    [self updateMaterialReturnDetailWithArray:returnDetailArray];
    [self updateMaterialRelationWithArray:relationArray];
    [self updateMaterialReceipientWithArray:stockOutArray];
    return executeResult;
}

- (BOOL)updateMaterialReturnStatusWithMaterialReturnId:(NSInteger)returnId
{
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        sql = @"SELECT * FROM 'materialReturn' WHERE returnId=?;";
        FMResultSet* set = [db executeQuery:sql, @(returnId)];
        if ([set next]) {
            sql = @"UPDATE 'materialReturn' SET returnStatus =? WHERE returnId =?;";
            executeResult = [db executeUpdate:sql, @(YWMaterialStatusHaveReturned), @(returnId)];
        }
        if (!executeResult) {
            *rollback = YES;
        };
        
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)deleteMaterialReturnWithReturnId:(NSInteger)returnId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"delete FROM 'materialReturn' WHERE returnId=?";
        executeResult = [db executeUpdate:sql, @(returnId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray*)materialReturnWithPage:(NSInteger)page
{
    __block NSMutableArray* materialReturnArray = [NSMutableArray array];
//    __block NSMutableArray * returnTimeArray = [NSMutableArray array];
    NSInteger showCount = page * 10;
//    NSInteger userId = [YWLoginTool loginUser].uniqueId;
    /*
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *selectSql = [NSString stringWithFormat:@"SELECT distinct yearMonthDay FROM 'materialReturn' WHERE returnStatus = %lu ORDER BY returnTime DESC;",(unsigned long)YWMaterialStatusHaveReturned];
        //creatorId =%ld OR returnerId = %ld  OR carbonCopyIds =%ld AND
        FMResultSet* resuleSet = [db executeQuery:selectSql];
        while ([resuleSet next]) {
            NSString *yearMonthDay = [resuleSet stringForColumn:@"yearMonthDay"];
            if (![returnTimeArray containsObject:yearMonthDay]) {
                [returnTimeArray addObject:yearMonthDay];
            }
        }
        [returnTimeArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull yearMonthDay, NSUInteger idx, BOOL * _Nonnull stop) {
            NSMutableArray *tempArray = [NSMutableArray array];
            NSString *sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReturn' WHERE yearMonthDay = ? ORDER BY returnTime DESC LIMIT %ld;",(long)showCount];
            FMResultSet* set = [db executeQuery:sql, yearMonthDay];
            while ([set next]) {
                YWMaterialReturn* materialReturn = [self materialReturnFMResultSet:set FMDatabase:db];
                [tempArray addObject:materialReturn];
            }
            if (tempArray.count>0) {
                [materialReturnArray addObject:tempArray];
            }
        }];
*/
        [self.dbQeue inDatabase:^(FMDatabase* db) {
            NSString* sql = nil;
            FMResultSet* set = nil;
            sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReturn' WHERE returnStatus = %lu ORDER BY returnTime DESC LIMIT %ld;",(unsigned long)YWMaterialStatusHaveReturned,(long)showCount];
            set = [db executeQuery:sql];
            
            NSString *lastDate = nil;
            NSMutableArray *array = nil;
            while ([set next]) {
                YWMaterialReturn* materialReturn = [self materialReturnFMResultSet:set FMDatabase:db];
                NSString *timeDate = [YWCommonTool showChineseFormatterDate:materialReturn.returnTime/1000];
                if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                    lastDate = timeDate;
                    array = [NSMutableArray array];
                }
                [array addObject:materialReturn];
                if (![materialReturnArray containsObject:array] && array.count) {
                    [materialReturnArray addObject:array];
                }
            }
            
//        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'materialReturn' WHERE returnStatus = %d ORDER BY returnTime DESC LIMIT %ld;",YWMaterialStatusHaveReturned, (long)showCount];
//        //WHERE returnStatus =?
//        FMResultSet* set = [db executeQuery:sql, @(2)];
//        while ([set next]) {
//            YWMaterialReturn* materialReturn = [self materialReturnFMResultSet:set FMDatabase:db];
//            [materialReturnArray addObject:materialReturn];
//        }
        [db closeOpenResultSets];
    }];
    return materialReturnArray.count ? materialReturnArray : nil;
}

- (YWMaterialReturn*)materialReturnFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialReturn* materialReturn = [[YWMaterialReturn alloc] init];
    materialReturn.materialRelationId = [set intForColumn:@"materialRelationId"];
    materialReturn.returnSequence = [set stringForColumn:@"returnSequence"];
    materialReturn.returnId = [set intForColumn:@"returnId"];
    materialReturn.note = [set stringForColumn:@"note"];
    materialReturn.createTime = [set longLongIntForColumn:@"createTime"];
    materialReturn.returnTime = [set longLongIntForColumn:@"updateTime"];
    materialReturn.yearMonthDay = [set stringForColumn:@"yearMonthDay"];
    materialReturn.returnStatus = [set intForColumn:@"returnStatus"];
    materialReturn.status = [set intForColumn:@"status"];
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(creatorId)];
    if ([authorSet next]) {
        materialReturn.creator = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
    }
    NSInteger returnerId = [set intForColumn:@"returnerId"];
    NSString* receiversql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* receiverSet = [db executeQuery:receiversql, @(returnerId)];
    if ([receiverSet next]) {
        materialReturn.returner = [[YWDBTool shareDBTool] handlerWithSet:receiverSet];
    }
    
    //读出的copyids是一个字符串，要通过遍历把它拼成一个数组然后再赋值
    NSString* copyIds = [set stringForColumn:@"carbonCopyIds"];
    NSArray* copidsArray = [copyIds componentsSeparatedByString:@","];
    NSMutableArray* copies = [NSMutableArray array];
    [copidsArray enumerateObjectsUsingBlock:^(NSString* _Nonnull uniqueId, NSUInteger idx, BOOL* _Nonnull stop) {
        NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
        FMResultSet* authorSet = [db executeQuery:authorsql, uniqueId];
        if ([authorSet next]) {
            YWHandler* handler = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
            [copies addObject:handler];
        }
    }];
    materialReturn.observers = copies;


    NSInteger stockOutId = [set intForColumn:@"stockOutId"];
    NSString* stockOutSql = @"SELECT * FROM 'materialReceipient' WHERE stockOutId =?;";
    FMResultSet* stockOutSet = [db executeQuery:stockOutSql, @(stockOutId)];
    if ([stockOutSet next]) {
        materialReturn.materialStockOut = [self materialReceipientFMResultSet:stockOutSet FMDatabase:db];
    }

    NSInteger returnId = [set intForColumn:@"returnId"];
    NSMutableArray* detailArray = [NSMutableArray array];
    NSString* sql = @"SELECT * FROM 'materialReturnDetail' WHERE returnId = ?;";
    FMResultSet* detailSet = [db executeQuery:sql, @(returnId)];
    while ([detailSet next]) {
        YWMaterialReturnDetail* detail = [self materialReturnDetailFMResultSet:detailSet FMDatabase:db];
        [detailArray addObject:detail];
    }
    materialReturn.details = detailArray;
    
    NSMutableArray* tempArray = [NSMutableArray array];
    sql = @"SELECT * FROM 'reply' WHERE relationId=? AND relationType=? ORDER BY replyTime;";
    FMResultSet* newset = [db executeQuery:sql, @(returnId), @(YWReplyRelationTypeMaterialReturn)];
    while ([newset next]) {
        YWReply* reply = [[YWReplyDBManager shareManager] replyWithFMResultSet:newset FMDatabase:db];
        [tempArray addObject:reply];
    }
    materialReturn.replies = tempArray;

    
    return materialReturn;
}
- (YWMaterialReturn*)materialReturnWithMaterialReturnId:(NSInteger)materialReturnId
{
    __block YWMaterialReturn* materialReturn = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialReturn' WHERE returnId = ?;";
        //AND returnStatus=2
        FMResultSet* set = [db executeQuery:sql, @(materialReturnId)];
        if ([set next]) {
            materialReturn = [self materialReturnFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialReturn;
}
#pragma mark - 物料归还明细表的更新、取值
NSString*const kSqlCreateMaterialReturnDetailTable = @"CREATE TABLE IF NOT EXISTS 'materialReturnDetail' (detailId integer,returnId integer,stockId integer,quantity integer,createTime timestamp,updateTime timestamp,status integer);";

- (BOOL)createMaterialReturnDetailTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialReturnDetailTable];
}

- (BOOL)updateMaterialReturnDetailWithArray:(NSArray*)materialReturnDetailArray
{
    if (materialReturnDetailArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialReturnDetailArray = materialReturnDetailArray;
    __block NSMutableArray* stockArray = [NSMutableArray array];
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialReturnDetailArray enumerateObjectsUsingBlock:^(YWMaterialReturnDetail* _Nonnull materialReturnDetail, NSUInteger idx, BOOL* _Nonnull stop) {

            if (materialReturnDetail.materialStock) {
                [stockArray addObject:materialReturnDetail.materialStock];
            }
            sql = @"SELECT * FROM 'materialReturnDetail' WHERE detailId=?;";
            FMResultSet* set = [db executeQuery:sql, @(materialReturnDetail.detailId)];
            if ([set next]) {
                sql = @"UPDATE 'materialReturnDetail' SET returnId =?,stockId =?,quantity =?,createTime =?,updateTime =?,status =? WHERE detailId =?;";
                executeResult = [db executeUpdate:sql, @(materialReturnDetail.returnId), @(materialReturnDetail.materialStock.stockId), @(materialReturnDetail.quantity), @(materialReturnDetail.createTime), @(materialReturnDetail.updateTime), @(materialReturnDetail.status), @(materialReturnDetail.detailId)];
            }
            else {
                sql = @"INSERT INTO 'materialReturnDetail' (detailId,returnId,stockId,quantity,createTime,updateTime,status) VALUES (?, ?, ?, ?, ?,?, ?);";
                executeResult = [db executeUpdate:sql, @(materialReturnDetail.detailId), @(materialReturnDetail.returnId), @(materialReturnDetail.materialStock.stockId), @(materialReturnDetail.quantity), @(materialReturnDetail.createTime), @(materialReturnDetail.updateTime), @(materialReturnDetail.status)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [self updateMaterialStockWithArray:stockArray];
    return executeResult;
}
- (YWMaterialReturnDetail*)materialReturnDetailFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialReturnDetail* materialReturnDetail = [[YWMaterialReturnDetail alloc] init];
    materialReturnDetail.detailId = [set intForColumn:@"detailId"];
    materialReturnDetail.returnId = [set intForColumn:@"returnId"];

    materialReturnDetail.quantity = [set intForColumn:@"quantity"];
    materialReturnDetail.createTime = [set longLongIntForColumn:@"createTime"];
    materialReturnDetail.updateTime = [set longLongIntForColumn:@"updateTime"];
    materialReturnDetail.status = [set intForColumn:@"status"];

    NSInteger stockId = [set intForColumn:@"stockId"];
    NSString* authorsql = @"SELECT * FROM 'materialStock' WHERE stockId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(stockId)];
    if ([authorSet next]) {
        materialReturnDetail.materialStock = [self materialStockWithFMResultSet:authorSet FMDatabase:db];
    }
    return materialReturnDetail;
}

#pragma mark - 物料关系表更新、取值
NSString*const kSqlCreateMaterialRelationTable = @"CREATE TABLE IF NOT EXISTS 'materialRelation' (applyId integer,stockOutId integer NOT NULL,returnId integer);";

- (BOOL)createMaterialRelationTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialRelationTable];
}

- (BOOL)updateMaterialRelationWithArray:(NSArray*)materialRelationArray
{
    if (materialRelationArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMaterialRelationArray = materialRelationArray;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMaterialRelationArray enumerateObjectsUsingBlock:^(YWMaterialRelation* _Nonnull materialRelation, NSUInteger idx, BOOL* _Nonnull stop) {
            if (materialRelation.stockOutId!=0) {
                sql = @"SELECT * FROM 'materialRelation' WHERE stockOutId=?;";
                FMResultSet* set = [db executeQuery:sql, @(materialRelation.stockOutId)];
                if ([set next]) {
                    if (materialRelation.applyId==0){
                        sql = @"UPDATE 'materialRelation' SET stockOutId =?,returnId =? WHERE stockOutId =?;";
                        executeResult = [db executeUpdate:sql, @(materialRelation.stockOutId), @(materialRelation.returnId), @(materialRelation.stockOutId)];
                    }else if (materialRelation.returnId==0) {
                        sql = @"UPDATE 'materialRelation' SET applyId =?,stockOutId =? WHERE stockOutId =?;";
                        executeResult = [db executeUpdate:sql, @(materialRelation.applyId), @(materialRelation.stockOutId),@(materialRelation.stockOutId)];
                    }else{
                        sql = @"UPDATE 'materialRelation' SET applyId =?,stockOutId =?,returnId =? WHERE stockOutId =?;";
                        executeResult = [db executeUpdate:sql, @(materialRelation.applyId), @(materialRelation.stockOutId), @(materialRelation.returnId), @(materialRelation.stockOutId)];
                    }            }
                else {
                    sql = @"INSERT INTO 'materialRelation' (applyId,stockOutId,returnId) VALUES (?, ?, ?);";
                    executeResult = [db executeUpdate:sql, @(materialRelation.applyId), @(materialRelation.stockOutId), @(materialRelation.returnId)];
                }
                if (!executeResult) {
                    *stop = YES;
                    *rollback = YES;
                };
            }
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (YWMaterialRelation*)materialRelationWithMaterialApplyId:(NSInteger)materialApplyId
{
    __block YWMaterialRelation* materialRelation = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialRelation' WHERE applyId = ?;";
        FMResultSet* set = [db executeQuery:sql, @(materialApplyId)];
        if ([set next]) {
            materialRelation = [self materialRelationWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialRelation;
}

- (YWMaterialRelation*)materialRelationWithMaterialReceipientId:(NSInteger)materialReceipientId
{
    __block YWMaterialRelation* materialRelation = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialRelation' WHERE stockOutId = ?;";
        FMResultSet* set = [db executeQuery:sql, @(materialReceipientId)];
        if ([set next]) {
            materialRelation = [self materialRelationWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialRelation;
}

- (YWMaterialRelation*)materialRelationWithMaterialReturnId:(NSInteger)materialReturnId
{
    __block YWMaterialRelation* materialRelation = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialRelation' WHERE returnId = ?;";
        FMResultSet* set = [db executeQuery:sql, @(materialReturnId)];
        if ([set next]) {
            materialRelation = [self materialRelationWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return materialRelation;
}

- (YWMaterialRelation*)materialRelationWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialRelation* materialRelation = [[YWMaterialRelation alloc] init];
    materialRelation.applyId = [set intForColumn:@"applyId"];
    materialRelation.stockOutId = [set intForColumn:@"stockOutId"];
    materialRelation.returnId = [set intForColumn:@"returnId"];
    return materialRelation;
}

#pragma mark - 物料流转信息表更新、取值
NSString*const kSqlCreateMaterialCirculationTable = @"CREATE TABLE IF NOT EXISTS 'materialCirculation' (infoId integer,materialRelationId integer,authorId integer,recipientId integer,createTime timestamp,note text,type smallint);";

- (BOOL)createMaterialCirculationTable
{
    return [[self fmdb] executeUpdate:kSqlCreateMaterialCirculationTable];
}

- (BOOL)updateMaterialCirculationInfoWithArray:(NSArray*)circulationInfoArray{
    if (circulationInfoArray.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockCirculationInfoArray = circulationInfoArray;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockCirculationInfoArray enumerateObjectsUsingBlock:^(YWMaterialCirculation * _Nonnull circulationInfo, NSUInteger idx, BOOL* _Nonnull stop) {
            
            if (idx == 0) {
                sql = @"DELETE FROM 'materialCirculation' WHERE materialRelationId=?;";
                executeResult = [db executeUpdate:sql,@(circulationInfo.materialRelationId)];
                if (executeResult) {
                    sql = @"INSERT INTO 'materialCirculation' (infoId,materialRelationId,authorId,recipientId,createTime,note,type) VALUES (?, ?, ?, ?, ?, ?, ?);";
                    executeResult = [db executeUpdate:sql, @(circulationInfo.infoId),@(circulationInfo.materialRelationId),@(circulationInfo.author.uniqueId),@(circulationInfo.recipient.uniqueId),@(circulationInfo.createTime),circulationInfo.note,@(circulationInfo.type)];
                }
                
            }else{
                sql = @"SELECT * FROM 'materialCirculation' WHERE infoId=? AND type=?;";
                FMResultSet* set = [db executeQuery:sql, @(circulationInfo.infoId), @(circulationInfo.type)];
                if ([set next]) {
                    sql = @"UPDATE 'materialCirculation' SET materialRelationId=?,authorId=?,recipientId=?,createTime=?,note=? WHERE infoId=? AND type=?;";
                    executeResult = [db executeUpdate:sql, @(circulationInfo.materialRelationId),@(circulationInfo.author.uniqueId),@(circulationInfo.recipient.uniqueId),@(circulationInfo.createTime),circulationInfo.note, @(circulationInfo.infoId), @(circulationInfo.type)];
                }
                else {
                    sql = @"INSERT INTO 'materialCirculation' (infoId,materialRelationId,authorId,recipientId,createTime,note,type) VALUES (?, ?, ?, ?, ?, ?, ?);";
                    executeResult = [db executeUpdate:sql, @(circulationInfo.infoId),@(circulationInfo.materialRelationId),@(circulationInfo.author.uniqueId),@(circulationInfo.recipient.uniqueId),@(circulationInfo.createTime),circulationInfo.note,@(circulationInfo.type)];
                }
            }
            
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray *)materialCirculationInfoArrayWithMaterialRelationId:(NSInteger)materialRelationId{
    __block NSMutableArray* circulationInfoArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'materialCirculation' WHERE materialRelationId=? order by createtime DESC;";//AND infoId>0,新构建的流转infoId是负的
        FMResultSet* set = [db executeQuery:sql, @(materialRelationId)];
        while ([set next]) {
            YWMaterialCirculation* circulationInfo = [self materialCirculationInfoWithFMResultSet:set FMDatabase:db];
            [circulationInfoArray addObject:circulationInfo];
        }
        [db closeOpenResultSets];
    }];
    return circulationInfoArray;
}

- (YWMaterialCirculation *)materialCirculationInfoWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaterialCirculation* circulationInfo = [[YWMaterialCirculation alloc] init];
    circulationInfo.infoId = [set intForColumn:@"infoId"];
    circulationInfo.materialRelationId = [set intForColumn:@"materialRelationId"];
    NSInteger authorId = [set intForColumn:@"authorId"];
    NSString* authorsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* authorSet = [db executeQuery:authorsql, @(authorId)];
    if ([authorSet next]) {
        circulationInfo.author = [[YWDBTool shareDBTool] handlerWithSet:authorSet];
    }
    NSInteger recipientId = [set intForColumn:@"recipientId"];
    NSString* recipientsql = @"SELECT * FROM 'handler' WHERE uniqueId =?;";
    FMResultSet* recipientSet = [db executeQuery:recipientsql, @(recipientId)];
    if ([recipientSet next]) {
        circulationInfo.recipient = [[YWDBTool shareDBTool] handlerWithSet:recipientSet];
    }
    circulationInfo.createTime = [set longLongIntForColumn:@"createTime"];
    circulationInfo.note = [set stringForColumn:@"note"];
    circulationInfo.type = [set intForColumn:@"type"];
    return circulationInfo;
}
@end
